/*
declare @knownList varchar(max)
declare @colours varchar(max)
declare @maxLevels varchar(max)

set @knownList = 'Dispel Magic 6,Dispel Magic 7,Dispel Magic 8,Dumbness 1-5'
set @colours = 'Blue'
set @maxLevels = '8'
*/

/*
 *	Michael Harper-Jones
 *	22 June 2012
 *	Get all allowed spells for a character.
 *	Any already known spells are included (whether allowed or not.)
 */
create procedure [hqpad-character-allowed-spells]
	@rulesId int,
	@knownList varchar(max),
	@colours varchar(max),
	@maxLevels varchar(max),
	@primaryColours varchar(max),
	@specialistColours varchar(max)
as
begin

	declare @colourLevelTable Table
	(
		Colour varchar(50),
		MaxLevel int
	)

	insert @colourLevelTable (Colour, MaxLevel)
	select x.Item, convert(int, y.Item)
	from udf_ItemParse(@colours, ',') x 
	inner join udf_ItemParse(@maxLevels, ',') y on y.Pos = x.Pos

	declare @maxLevel int
	select @maxLevel = MAX(maxLevel) from @colourLevelTable

	insert @colourLevelTable (Colour, MaxLevel)
	values ('All', @maxLevel)

	--select * from @colourLevelTable

	select s.* 
	from spell s
	inner join @colourLevelTable c on c.Colour = s.Colour and s.[Level] <= c.MaxLevel
	where IsCommon = 0
	and s.RuleSetID = @rulesId
	and (
		s.GroupName in (select Item from udf_ItemParse(@knownList, ',')) 
		or s.RequiredGroupName is null 
		or s.RequiredGroupName in (select Item from udf_ItemParse(@knownList, ',')))
	and (s.IsSpecialist = 0 
		or (s.IsSpecialist = 1 and s.Colour in (select Item from udf_ItemParse(@specialistColours, ','))) )
	and (s.IsPrimary = 0
		or (s.IsPrimary = 1 and s.Colour in (select Item from udf_ItemParse(@primaryColours, ','))) )
end